package com.liuxl.db.tool;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import com.github.pagehelper.Page;
import com.github.pagehelper.dialect.ReplaceSql;
import com.github.pagehelper.dialect.replace.SimpleWithNolockReplaceSql;
import com.github.pagehelper.parser.CountSqlParser;
import com.github.pagehelper.parser.OrderByParser;
import com.google.common.collect.Lists;
import com.liuxl.db.JdbcUtils;
import com.liuxl.db.bo.Column;

import cn.hutool.core.util.StrUtil;

public abstract class AbstractDatabaseTool implements DatabaseTool {
	
	protected ReplaceSql replaceSql = new SimpleWithNolockReplaceSql();

	
	/**
	 * 封装分页SQL（插入排序SQL）
	 * @param page 
	 * @param sql
	 * @return
	 */
	public String getPageSql(Page<Map<String,Object>> page,String sql) {
		String orderBy = page.getOrderBy();
		if (StrUtil.isNotBlank(orderBy)) {
			sql = OrderByParser.converToOrderBySql(sql, orderBy);
		}
		return getPageSql(sql);
	}
	
	@Override
	public Page<Map<String, Object>> retrievePage(DataSource datasource, Page<Map<String,Object>> page, String sql,
			List<Object> parameters) throws SQLException {
		//查询总行数据
		if (page == null) {
			page = new Page<>();
			List<Map<String, Object>> results = JdbcUtils.executeQuery(datasource, sql, parameters);
			page.addAll(results);
			return page;
		}
		String countSql = getCountSql(sql);//解析SQL，将SQL转换为countSQL，统计查询数据条数
		List<Object[]> countResult = JdbcUtils.executeQuery2(datasource, countSql, parameters);
		Object obj = countResult.get(0)[0];
		Integer count = obj == null ? 0 : Integer.valueOf(obj.toString());
		page.setTotal(count);
		if (count == 0) {//如果总行数为零，则直接返回Page对象
			page.addAll(Lists.newArrayList());
			return page;
		}
		String pageSql = getPageSql(page, sql);//解析SQL，将SQL转换为分页SQL。
		addPageParameter(page, parameters);//填充分页参数
		List<Map<String, Object>> results = JdbcUtils.executeQuery(datasource, pageSql, parameters);
		page.addAll(results);
		return page;
	}
	
	
	/**
	 * 封装分页SQL
	 * @param sql
	 * @return
	 */
	public abstract String getPageSql(String sql);
	
	/**
	 * 封装统计（COUNT）SQL 分页需要
	 * @param sql 查询SQL
	 * @return
	 */
	public String getCountSql(String sql) {
		CountSqlParser countSqlParser = new CountSqlParser();
		String countSql = countSqlParser.getSmartCountSql(sql);
		return countSql;
	}
	

	/**
	 * 添加分页参数
	 * @param page
	 * @param parameters
	 */
    public abstract void addPageParameter(Page<Map<String,Object>> page,List<Object> parameters);
    
    @Override
    public void addColumn(DataSource dataSource, String tableName, Column column) throws SQLException {
    	String name = column.getName();
		Boolean isNullable = column.getIsNullable();
		String columnType = column.bulidColumnType();
    	String sql = "alter table "+tableName+" add "+name+" "+ columnType+" "+(isNullable ? "" : " not null ");
    	JdbcUtils.execute(dataSource, sql);
    }
    
    @Override
    public void dropColumn(DataSource dataSource, String tableName, String columnName) throws SQLException {
    	String sql = "alter table "+tableName+" drop column "+columnName;
    	JdbcUtils.execute(dataSource, sql);
    }
    
    @Override
    public void modifyColumn(DataSource dataSource, String tableName, Column column) throws SQLException {
    	String name = column.getName();
		Boolean isNullable = column.getIsNullable();
		String columnType = column.bulidColumnType();
    	String sql = "alter table "+tableName+" modify "+name+" "+ columnType+" "+(isNullable ? "" : " not null ");
    	JdbcUtils.execute(dataSource, sql);
    }
    
}
